import pymysql
import xlwt


# 统计商品销售情况
def exe_db():
    # 打开数据库连接，不指定数据库
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='xuhuan.5130638')
    # conn = pymysql.connect(host='139.155.88.162', port=3306, user='root', password='xuhuan.59')
    conn.select_db('linjiashop_backups')
    cur = conn.cursor()  # 获取游标

    # ================
    # 数据记录
    id_list = []
    name_list = []
    num_list = []
    total_list = []
    prict_list = []

    total_money = 0
    try:
        # print("===========================================================================================================")
        print("通知系统开始查询订单")
        cur.execute("select * from t_shop_order where (status>1 and status<=4)")  # 1未付款 2未发货 3已发货 4已完成
        # 使用 fetchone() 方法获取一条数据
        data = cur.fetchall()

        if data:
            for row in data:
                cur.execute("select * from t_shop_order_item where id_order="+str(row[0]))
                data_order_item = cur.fetchall()

                # print(row)
                total_money += float(row[18])
                for item in data_order_item:
                    if item[4] not in id_list:
                        id_list.append(item[4])
                        num_list.append(item[3])
                        total_list.append(item[8])
                        prict_list.append(item[7])
                        cur.execute("select * from t_shop_goods where id=" + str(item[4]))
                        goods = cur.fetchone()
                        name_list.append(goods[14])
                        try:
                            if item[6] != None:
                                cur.execute("select * from t_shop_goods_sku where id=" + str(item[6]))
                                sku = cur.fetchone()
                                name_list[id_list.index(item[4])] += sku[6]
                        except:
                            print("===============")
                    else:
                        num = num_list[id_list.index(item[4])]
                        num_list[id_list.index(item[4])] = int(num) + int(item[3])

                        money = total_list[id_list.index(item[4])]
                        total_list[id_list.index(item[4])] = int(money) + int(item[8])

            for index in range(len(total_list)):
                total_list[index] = float(total_list[index])/100
            for index in range(len(prict_list)):
                prict_list[index] = float(prict_list[index])/100

            # 生成报表
            work_book = xlwt.Workbook(encoding='utf-8')
            sheet = work_book.add_sheet('sheet表名')
            sheet.write(0, 0, '用户ID')
            sheet.write(0, 1, '商品名称')
            sheet.write(0, 2, '销售数量')
            sheet.write(0, 3, '销售金额')
            sheet.write(0, 4, '销售单价')
            # 统计求和
            sheet.write(0, 6, '销售总额')
            sheet.write(0, 7, '实际销售金额')
            sheet.write(0, 8, '优惠金额')

            sheet.write(1, 7, str(total_money/100))
            sheet.write(1, 6, "=SUM(D2:D"+str(len(id_list)+1)+")")
            sheet.write(1, 8, "=(G2-H2)")
            for index in range(len(id_list)):
                sheet.write(index+1, 0, id_list[index])
                sheet.write(index+1, 1, name_list[index])
                sheet.write(index+1, 2, num_list[index])
                sheet.write(index+1, 3, total_list[index])
                sheet.write(index+1, 4, prict_list[index])
            work_book.save('C:\\Users\\Administrator\\Desktop\\商品销售统计.xls')
        conn.commit()
    finally:
        try:
            cur.close()
            conn.close()
        except Exception as e1:
            print('sql执行成功异常：' + str(e1))


if __name__ == '__main__':
    exe_db()